package com.nus.database;

/**
 * Created by IntelliJ IDEA.
 * User: dell
 * Date: 9/16/12
 * Time: 12:18 AM
 * To change this template use File | Settings | File Templates.
 */
public class SQLStatements {
    public static final String LOGIN_SQL = "SELECT id FROM db_admin.users WHERE upper(loginname) = upper(?) AND password = ? AND USERSTATUS <> 2";
    public static final String USER_DATA_BY_LOGIN_NAME_SQL = "SELECT * FROM db_admin.users WHERE upper(loginname) = upper(?)";
    public static final String USER_DATA_BY_USER_ID_SQL = "SELECT * FROM db_admin.users WHERE id = ?";
    public static final String GET_ASSIGNED_PROJECTS = "SELECT * FROM db_admin.projects, db_admin.userproject WHERE projects.id = userproject.projectid AND userproject.userid = ?";
    public static final String CHECK_LEAVE_APPLICABLE_SQL = "SELECT leavedate, userid FROM db_admin.leaves WHERE userid = ? AND leavedate = ?";
    public static final String DECREASE_LEAVES = "UPDATE db_admin.users SET remainingleaves = (remainingleaves - 1) WHERE id = ?";
    public static final String ADD_LEAVE_REQUEST = "INSERT INTO db_admin.leaves (USERID, LEAVEDATE, APPLIED_DATE, STATUS) values (?, ?, sysdate, ?)";
    public static final String UPDATE_LEAVE_REQUEST = "UPDATE db_admin.leaves SET status = ? WHERE userid = ? AND leavedate = ?";
    public static final String REAPPLY_LEAVE_REQUEST = "UPDATE db_admin.leaves SET APPLIED_DATE = sysdate, VERIFIED_DATE = null, VERIFIED_BY = null, APPROVED_DATE = null, APPROVED_BY = null, STATUS = ?, REJECTED_BY = null, REJECTED_DATE = null  WHERE userid = ? AND leavedate = ?";
    public static final String INCREASE_LEAVES = "UPDATE db_admin.users SET remainingleaves = (remainingleaves + 1) WHERE id = ?";
    public static final String CHECK_LEAVE_CANCELABLE_SQL = "SELECT a.userid FROM db_admin.leaves a, db_admin.users b WHERE a.userid = b.id AND a.userid = ? AND a.LEAVEDATE=? AND ((b.USERTYPE in (1, 2, 4) AND (a.status in (3, 4, 5) AND a.LEAVEDATE < sysdate)) OR (b.USERTYPE = 3 AND a.status in (4, 5)))";
    public static final String CHECK_LEAVE_REJECTABLE_SQL = "SELECT userid FROM db_admin.leaves WHERE userid = ? AND leavedate = ? AND status in (3, 4, 5)";
    public static final String CHECK_LEAVE_VERIFIABLE_SQL = "SELECT userid FROM db_admin.leaves WHERE userid = ? AND leavedate = ? AND status = 1)";
    public static final String CHECK_LEAVE_REAPPLYALE_SQL = "SELECT userid FROM db_admin.leaves WHERE userid = ? AND leavedate = ? AND status not in (4, 5)";
    public static final String VIEW_LEAVE_HISTORY = "SELECT a.*, b.lastname as VERIFIED_NAME, c.lastname as APPROVED_NAME, d.lastname as REJECTED_NAME FROM db_admin.leaves a, db_admin.users b, " +
            "db_admin.users c, db_admin.users d WHERE a.VERIFIED_BY = b.id(+) AND a.APPROVED_BY = c.id(+) AND a.REJECTED_BY = d.id(+) AND a.userid = ?  order by a.APPLIED_DATE, a.LEAVEDATE desc";
    public static final String VERIFY_LEAVE_REQUEST = "UPDATE db_admin.leaves SET status = ?, VERIFIED_BY = ?, VERIFIED_DATE = sysdate WHERE userid = ? AND leavedate = ?";
    public static final String APPROVE_LEAVE_REQUEST = "UPDATE db_admin.leaves SET status = ?, APPROVED_BY = ?, APPROVED_DATE = sysdate WHERE userid = ? AND leavedate = ?";
    public static final String REJECT_LEAVE_REQUEST = "UPDATE db_admin.leaves SET status = ?, REJECTED_BY = ?, REJECTED_DATE = sysdate WHERE userid = ? AND leavedate = ?";
    public static final String GET_PENDING_LEAVES_SQL = "SELECT a.*, b.lastname as appliedName, c.lastname as verifiedname, c.lastname FROM db_admin.leaves a, db_admin.users b, db_admin.users c WHERE a.USERID = b.id(+) AND a.VERIFIED_BY = c.id(+) AND a.status = ? order by a.USERID, a.LEAVEDATE desc";
    public static final String CREATE_USER_SQL = "INSERT INTO db_admin.users (ID, LOGINNAME, PASSWORD, LASTNAME, FIRSTNAME, ADDRESS, PHONENO, DATEOFBIRTH, BASICSALARY, " +
            "USERTYPE, REMAININGLEAVES, USERSTATUS, USERSUBTYPE) VALUES (db_admin.USERS_SEQ.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    public static final String GET_ALL_USER_DATA_SQL = "SELECT * FROM db_admin.users order by USERTYPE desc";
    public static final String GET_ASSIGNED_ACTIVE_PROJECTS = "SELECT USERID FROM db_admin.USERPROJECT a, db_admin.PROJECTS b WHERE a.projectid = b.id AND a.userid = ? AND b.statusid = ?";
    public static final String UPDATE_USER_STAUS_SQL = "UPDATE db_admin.users SET userstatus = ? WHERE id = ?";
    public static final String UPDATE_USER_SQL = "UPDATE db_admin.users SET LASTNAME = ?, FIRSTNAME = ?, ADDRESS = ?, PHONENO = ?, DATEOFBIRTH = ?, BASICSALARY = ?, " +
            "USERTYPE = ?, REMAININGLEAVES = ?, USERSUBTYPE = ? WHERE LOGINNAME = ?";
    public static final String CREATE_PROJECT_SQL = "INSERT INTO db_admin.projects (ID, NAME, STARTDATE, ESTIMATEDENDDATE, DEADLINE, STATUSID) VALUES (?, ?, ?, ?, ?, ?)";
    public static final String GET_PROJECT_ID_SQL = "SELECT db_admin.PROJECTS_SEQ.nextval FROM dual";
    public static final String GET_ALL_PROJECT_DATA_SQL = "SELECT * FROM db_admin.projects";
    public static final String GET_PROJECT_USERS = "SELECT * FROM db_admin.users WHERE USERTYPE in (1, 2) AND USERSTATUS = 1 order by usertype asc";
    public static final String VALIDATE_LOGIN_NAME = "SELECT ID FROM db_admin.users WHERE loginName = ?";
    public static final String ASSIGN_USER_TO_PROJECT = "INSERT INTO db_admin.USERPROJECT (USERID, PROJECTID, ASSIGNEDDATE) VALUES (?, ?, sysdate)";
    public static final String GET_PROJECT_DATA_SQL = "SELECT * FROM db_admin.projects WHERE id = ?";
    public static final String GET_PROJECT_ASSIGNEES = "select * from db_admin.users, db_admin.userproject where users.id = userproject.userid and userproject.projectid =? order by users.usertype desc";
    public static final String DELETE_PROJECT_SQL = "UPDATE db_admin.projects SET statusid = 3 WHERE id = ?";
    public static final String UPDATE_PROJECT_SQL = "UPDATE db_admin.projects SET NAME = ?, STARTDATE = ?, ESTIMATEDENDDATE = ?, DEADLINE = ?, STATUSID = ? WHERE id = ?";
    public static final String DELTE_USER_OF_PROJECT = "DELETE FROM db_admin.userproject WHERE USERID = ? AND PROJECTID = ?";
    public static final String CHANGE_PASSWORD = "UPDATE db_admin.users SET PASSWORD = ? WHERE ID = ?";
    public static final String DELETE_ASSIGNED_LIST = "DELETE FROM db_admin.userproject WHERE userproject.userid = ?";
    public static final String CREATE_USER_SP = "call db_admin.CREATE_USER(?,?)";
    public static final String UPDATE_USER_PASSWORD_SP = "call db_admin.UPDATE_USER(?,?)";
    public static final String DELETE_USER_PASSWORD_SP = "call DB_ADMIN.DELETE_USER(?, ?)";
    public static final String GET_PROJECT_REMARKS_BY_PROJECT_ID = "SELECT * from db_admin.projectremarks, db_admin.users WHERE userid = db_admin.users.id and projectid = ?";
    public static final String CRAETE_PROJECT_REMARK = "INSERT INTO db_admin.projectremarks (id, projectid, data, userid) values (db_admin.PROJECT_REMARKS_SEQ.nextval, ?, ?, ?)";
    public static final String DELETE_PROJECT_REMARKS_BY_ID = "DELETE from db_admin.projectremarks WHERE id = ?";
    public static final String UPDATE_PROJECT_REMARK = "UPDATE db_admin.projectremarks SET DATA = ? where id = ?";
    public static final String GET_PROJECT_REMARKS_BY_ID = "SELECT * from db_admin.projectremarks, db_admin.users WHERE userid = db_admin.users.id and db_admin.projectremarks.id = ?";
    public static final String GET_USER_EVALUATIONS = "SELECT * FROM db_admin.USERASSESSMENTS, db_admin.users WHERE db_admin.users.id = db_admin.USERASSESSMENTS.enter_by AND  user_id = ?";
    public static final String CRAETE_USER_EVALUATION = "INSERT INTO db_admin.USERASSESSMENTS (id, USER_ID, EVALUATION, ENTER_BY, ENTER_DATE) values (db_admin.USER_ASSESSMENTS_SEQ.nextval, ?, ?, ?, sysdate)";
    public static final String GET_USER_EVALUATION_BY_ID = "SELECT * FROM db_admin.USERASSESSMENTS, db_admin.users WHERE db_admin.users.id = db_admin.USERASSESSMENTS.enter_by AND  db_admin.USERASSESSMENTS.id = ?";
    public static final String UPDATE_USER_EVALUATION = "UPDATE db_admin.USERASSESSMENTS SET EVALUATION = ? where id = ?";
    public static final String DELETE_USER_EVALUATION = "DELETE db_admin.USERASSESSMENTS where id = ?";
    public static final String BLOCK_USER = "UPDATE db_admin.USERS set status = 3 WHERE id = ?";
    public static final String CLEAR_FAILED_LOGIN_ATTEMPTS = "UPDATE db_admin.USERS set FAILED_LOGIN_ATTEMPTS = 0, USERSTATUS = 1 WHERE id = ?";
    public static final String GET_FAILED_LOGIN_ATTEMPTS = "SELECT FAILED_LOGIN_ATTEMPTS FROM db_admin.USERS WHERE id = ?";
    public static final String GET_LAST_FAILED_LOGIN_TIME = "SELECT LAST_FAILED_LOGIN_TIME FROM db_admin.USERS WHERE id = ?";
    public static final String INCREMENT_FAILED_LOGIN = "UPDATE db_admin.USERS SET LAST_FAILED_LOGIN_TIME = sysdate, FAILED_LOGIN_ATTEMPTS = FAILED_LOGIN_ATTEMPTS + 1 WHERE id = ?";
    public static final String UPDATE_LAST_LOGIN_FAILED_TIME = "UPDATE db_admin.USERS SET LAST_FAILED_LOGIN_TIME = sysdate WHERE id = ?";
    public static final String ASSIGN_LABEL = "call SA_USER_ADMIN.SET_USER_LABELS (?,?,?,?,?,?,?)";


}